package org.sjm.samples.dao;

import java.util.List;

import org.hibernate.transform.Transformers;
import org.sjm.samples.model.ExamScore;
import org.sjm.samples.model.ExamScoreVO;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class ExamScoreDAOImpl extends BaseHibernateDAO<ExamScore> implements IExamScoreDAO {

	@Override
	public List<ExamScore> getScoreByUser(String userId) {
		return this.queryForList(" FROM ExamScore AS es WHERE es.user.userId=? ", new Object[]{userId});
	}

	@Override
	public List<ExamScore> FindAllScore() {
		return this.find(" FROM ExamScore AS es WHERE es.score > 0");
	}

	@Override
	public ExamScore getScore(String userId, Integer subId) {
		return this.queryForObject(" FROM ExamScore AS es WHERE es.user.userId=? AND es.subject.subId=? AND es.score > 0 ", new Object[]{userId, subId});
	}

	@Override
	public ExamScoreVO getScoreById(Integer scoreId) {
		StringBuilder hql = new StringBuilder();
		hql.append(" select user.userName as userName, sub.name as subjectName, es.examTime as examTime, es.score as score ");
		hql.append(" from ExamScore es, User user, Subject sub ");
		hql.append(" where es.user.userId = user.userId ");
		hql.append(" and es.subject.subId = sub.subId ");
		hql.append(" and es.id= :scoreId ");
		return (ExamScoreVO) this.createQuery(hql.toString())
				.setResultTransformer(Transformers.aliasToBean(ExamScoreVO.class))
				.setInteger("scoreId", scoreId)
				.uniqueResult();
	}

	@Override
	public void createExamScore(ExamScore score) {
		this.save(score);
	}

	@Override
	public void editScore(String userId, Integer subId, Integer score) {
		StringBuilder hql = new StringBuilder();
		hql.append(" UPDATE ExamScore es SET es.score = :score ");
		hql.append(" WHERE es.user.userId = :userId AND es.subject.id = :subId ");
		this.createQuery(hql.toString())
				.setInteger("score", score)
				.setString("userId", userId)
				.setInteger("subId", subId)
				.executeUpdate();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ExamScore> getScoreBySub(String query) {
		StringBuilder hql = new StringBuilder();
		hql.append(" FROM ExamScore es ");
		hql.append(" WHERE es.subject.name= :subName ");
		return this.createQuery(hql.toString())
				.setString("subName", query)
				.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ExamScore> getScoreByUserId(String query) {
		StringBuilder hql = new StringBuilder();
		hql.append(" FROM ExamScore es ");
		hql.append(" WHERE es.user.userId= :userId ");
		return this.createQuery(hql.toString())
				.setString("userId", query)
				.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ExamScore> getScoreByUserName(String query) {
		StringBuilder hql = new StringBuilder();
		hql.append(" FROM ExamScore es ");
		hql.append(" WHERE es.user.userName= :userName ");
		return this.createQuery(hql.toString())
				.setString("userName", query)
				.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ExamScore> getScoreByDate(String[] date) {
		StringBuilder hql = new StringBuilder();
		hql.append(" FROM ExamScore es ");
		hql.append(" WHERE DATE(es.examTime)>= :D1 AND DATE(es.examTime)<= :D2 ");
		return this.createQuery(hql.toString())
				.setString("D1", date[0])
				.setString("D2", date[1])
				.list();
	}

}
